package manager;

import java.rmi.RemoteException;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import org.apache.axis2.AxisFault;

import cs236369.hw5.RegistrationServiceStub;
import cs236369.hw5.RegistrationServiceStub.AddEndpoint;
import cs236369.hw5.RegistrationServiceStub.AddEndpointResponse;
import cs236369.hw5.RegistrationServiceStub.DeleteEndpoint;
import cs236369.hw5.RegistrationServiceStub.DeleteEndpointResponse;
import utils.DBConnector;

/**
 * Application Lifecycle Listener implementation class ContextListener
 *
 */
public class ContextListener implements ServletContextListener {

    /**
     * Default constructor. 
     */
    public ContextListener() {
        // TODO Auto-generated constructor stub
    }

	/**
     * @see ServletContextListener#contextInitialized(ServletContextEvent)
     */
    public void contextInitialized(ServletContextEvent arg0) {
    	// TODO: types in the table
    	
    	DBConnector.setContext(arg0.getServletContext());
    	
    	DBConnector dbConn = null;
    	try {
    		dbConn = new DBConnector();
    		Statement stmt = dbConn.getConnection().createStatement(); 
    	
		
			stmt.executeUpdate("CREATE TABLE IF NOT EXISTS users "   +
			                   "(username VARCHAR(32), "             + 
			                   "password VARCHAR(32), "              + 
	                           "first_name VARCHAR(32), "            + 
	                           "last_name VARCHAR(32), "             +
	                           "phone_number VARCHAR(32), "          +
	                           "is_admin BOOL, "                     +  
	                           "PRIMARY KEY(username));");
			
			stmt.executeUpdate("CREATE TABLE IF NOT EXISTS courses "   +
	                "(course_name VARCHAR(64), "             		   + 
	                "description VARCHAR(256), "              	       + 
	                "credit_points DECIMAL(3,1), "            	       + 
	                "capacity SMALLINT, "                     		   +
	                "group_number SMALLINT, "						   +
	                "is_active BOOL,       "						   +
	                "PRIMARY KEY(course_name));");
			
			stmt.executeUpdate("CREATE TABLE IF NOT EXISTS course_sessions "  +
	             "(course_name VARCHAR(64), "             			   		  + 
	             "session_day TINYINT, "            	                      +
	             "session_start_time TINYINT, "              	       	  +
	             "session_end_time TINYINT, "              	      	  +
	             "PRIMARY KEY(course_name, session_start_time, session_day), "       +
	             "FOREIGN KEY(course_name) " 								  +
	             "REFERENCES courses(course_name) "							  +
	             "ON DELETE CASCADE);");	
			/* The on delete cascade statement will cause the DB to delete
			 * records from the table course_sessions as reaction to deleting 
			 * a corresponding record in courses.
			 */
			
			stmt.executeUpdate("CREATE TABLE IF NOT EXISTS user_courses "  +
		             "(course_name VARCHAR(64), "      			   		   + 
		             "username VARCHAR(32), "							   +
		             "PRIMARY KEY(course_name, username), "				   +		
	                 "FOREIGN KEY(course_name) " 						   +
	                 "REFERENCES courses(course_name) "					   +
	                 "ON DELETE CASCADE, " 								   +
	                 "FOREIGN KEY(username) " 							   +
	                 "REFERENCES users(username) " 						   +
	                 "ON DELETE CASCADE); " );
			
			
			/*Same logic on deleting as in the table course_cessions
			 * when deleting a user or a course
			 */
			
			stmt.executeUpdate(
					"CREATE TABLE IF NOT EXISTS user_stylesheets "  +
					"(username VARCHAR(32), " 						+
					"style_name VARCHAR(64), " 						+
					"xslt LONGTEXT, "								+
					"FOREIGN KEY(username) " 						+
					"REFERENCES users(username) "					+
					"ON DELETE CASCADE, " 							+
					"PRIMARY KEY(username, style_name)); ");
    	}
    	catch(SQLException e) {
    		// TODO Auto-generated catch block
    		e.printStackTrace();
    	} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	finally {
			try {
				dbConn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
    	
    	// Register to yellow pages
    	RegistrationServiceStub stub = null;
		try {
			stub = new RegistrationServiceStub();
			AddEndpoint addEP = new AddEndpoint();            
	        addEP.setUrl("sadyabrandsgalus");      
	        AddEndpointResponse addEPResponse = stub.addEndpoint(addEP);
	        System.out.println("AddEndpoint method returned: " + addEPResponse.get_return() );
		} catch (AxisFault e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (RemoteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
        
    }

	/**
     * @see ServletContextListener#contextDestroyed(ServletContextEvent)
     */
    public void contextDestroyed(ServletContextEvent arg0) {
    	RegistrationServiceStub stub = null;
		try {
			stub = new RegistrationServiceStub();
			DeleteEndpoint deleteEP = new DeleteEndpoint();
		    deleteEP.setUrl("sadyabrandsgalus");
		    DeleteEndpointResponse delResponse = stub.deleteEndpoint(deleteEP);
		    System.out.println("DeleteEndpoint method returned: " + delResponse.get_return() );
		} catch (AxisFault e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (RemoteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
	
}
